package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.SalesOrder;

public class DBSalesOrder implements IFDBSalesOrder {
	private Connection con;
	
	public DBSalesOrder() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public ArrayList<SalesOrder> getAll(boolean retriveAssociation) {
		 return miscWhere("", retriveAssociation);
	}

	@Override
	public SalesOrder find(int SalesOrder, boolean retriveAssociation) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int insert(SalesOrder order) throws Exception {
		 //call to get the next ssn number
        
  
       int rc = -1;
	   String query="INSERT INTO SALESORDER(date, deliveryDate, deliveryStatus)  VALUES('"+
	  		order.getDate()  + "','"  +
		    order.getDeliveryDate()  + "','"  +
	  		order.getDeliveryStatus() + "')";
            
            
                     

       System.out.println("insert : " + query);
      try{ // insert new employee +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Order ikke oprettet");
          System.out.println("SQLException says: " + ex.getMessage());
         throw new Exception ("Order is not inserted correct");
       }
       return(rc);
	}

	@Override
	public int update(SalesOrder order) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int delete(SalesOrder order) {
		// TODO Auto-generated method stub
		return 0;
	}
	
	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 SalesOrder oObj = new SalesOrder();
		 oObj = buildSalesOrder(results);	
                 list.add(oObj);	
		}//end while
                 stmt.close();     
            }
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT id, date, deliveryDate, deliveryStatus  FROM SALESORDER";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	private SalesOrder buildSalesOrder(ResultSet results)
    {   SalesOrder oObj = new SalesOrder();
        try{ // the columns from the table emplayee  are used
        	oObj.setOrderid(results.getString("id"));
        	oObj.setDate(results.getString("date"));
        	oObj.setDeliveryDate(results.getString("deliveryDate"));
        	oObj.setDeliveryStatus(results.getString("deliveryStatus"));
        	
              
        }
       catch(Exception e)
       {
           System.err.println("error in building the product object");
       }
       return oObj;
    }
	
	

}
